﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Odbc;
using System.Data;
using MySql.Data.MySqlClient;
using MySql.Data;
using MySql;

namespace AMPSDAO.DAO.DatabaseManagement
{
    public class MySQLDAO: IDatabaseClient
    {

        public IDbTransaction Transaction
        {
            get;
            set;
        }
        public System.Data.IDbConnection Connection
        {
            get;
            set;
        }

        public MySQLDAO()
        {
            getConnection();
        }

        public System.Data.IDbConnection getConnection()
        {
            if (Connection == null)
            {
                //string strConnection = "Server=10.60.30.14;Database=servicedesk;Port=33366; User ID=root;Pooling=true;maximumpoolsize=1000;minimumPoolSize=0;Allow Zero Datetime=true";
                //string strConnection = "Dsn=HelpdeskDB;";
                //string strConnection = "Server=10.60.30.14;Database=servicedesk;Port=33366; User ID=root;Password=jd^g74Y895!f9g24V#;allow user variables=true";
                //string strConnection = "Server=10.199.18.120;Database=servicedesk;Port=33366; User ID=root;Password=jd^g74Y895!f9g24V#;allow user variables=true";
                //string strConnection = "Server=localhost;Database=storeonline;Port=3306; User ID=root;allow user variables=true;Allow Zero Datetime=true";
                //string strConnection = "Server=localhost;Database=storeonline;Port=3306; User ID=root;Password=123456;allow user variables=true";
                string strConnection = "Server=localhost;Database=ampsdb;Port=33366; User ID=root;Password=123456;allow user variables=true";
                Connection = null;
                try
                {
                    Connection = new MySqlConnection(strConnection);
                    
                }
                catch (Exception)
                {
                    Connection = null;
                }
            }
            return Connection;
        }

        

        public int ExecuteInsertCommand(string insertCmd)
        {
            int effectedRow = 0;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                MySqlCommand Cmd = new MySqlCommand(insertCmd, (MySqlConnection)Connection);
                if (Transaction != null)
                    Cmd.Transaction = (MySqlTransaction)Transaction;
                effectedRow = Cmd.ExecuteNonQuery();
                //Hủy đối tượng Cmd
                //Cmd.Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
            //#region hủy kết nối CSDL
            //if ((this.Connection != null) && (this.Connection.State == ConnectionState.Open))
            //{
            //    this.Connection.Close();
            //    this.Connection.Dispose();
            //}
            //#endregion

            return effectedRow;
        }

        public void CloseConnection(bool unsafeclose)
        {
            if (Transaction == null)
            {
                if(!unsafeclose)
                    throw new Exception("Transaction is null, If don't use transaction you must close connection with method CloseConnection(true)");
            }
            if (Connection.State == ConnectionState.Open)
            {
                if(Transaction != null)Transaction.Dispose();
                this.Connection.Close();
            }
        }

        public void Open()
        {
            if (Connection.State != ConnectionState.Open)
                Connection.Open();
            Transaction = Connection.BeginTransaction();
        }

        public void Commit()
        {
            Transaction.Commit();
        }

        public void RollBack()
        {
            Transaction.Rollback();
        }

        /// <summary>
        /// value: Select {0} From {1}
        /// example: FormatString(SelectCommand, ColumnNames, Table and Condition) 
        /// </summary>
        public string SelectCommand
        {
            get
            {
                return "Select {0} From {1}";
            }
        }
        /// <summary>
        /// value: Insert Into {0} Value {1}
        /// example: FormatString(InsertCommand, TableName, ColumnNames, Values) 
        /// </summary>
        public string InsertCommand
        {
            get
            {
                return "Insert Into {0}{1} Values {2}";
            }
        }
        /// <summary>
        /// value: Update {0} Set {1} Where {2}
        /// example: FormatString(UpdateCommand, TableName, ColumnName = value, Condition)
        /// </summary>
        public string UpdateCommand
        {
            get
            {
                return "Update {0} Set {1} Where {2}";
            }
        }
        /// <summary>
        /// value: Delete {0} From {1} Where {3}
        /// example: FormatString(DeleteCommand, TableDeleteNames, TableNames, Condition)
        /// </summary>
        public string DeleteCommand
        {
            get
            {
                return "Delete {0} From {1} Where {3}";
            }
        }


        public int ExecuteInsertCommand(string insertCmd, IParamenter[] param, IParamenter[] paramcondition = null)
        {
            int effectedRow = 0;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                MySqlCommand Cmd = new MySqlCommand(insertCmd, (MySqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                foreach (IParamenter item in param)
                {
                    MySqlParameter iparam = new MySqlParameter(item.ParameterName,item.Value);
                    if (item.DbType == DbType.String)
                        iparam.MySqlDbType = MySqlDbType.VarChar;
                    if (item.DbType == DbType.AnsiString)
                        iparam.MySqlDbType = MySqlDbType.Text;
                    Cmd.Parameters.Add(iparam);
                }
                if (paramcondition != null)
                {
                    foreach (IParamenter item in paramcondition)
                    {
                        MySqlParameter iparam = new MySqlParameter(item.ParameterName, item.Value);
                        if (item.DbType == DbType.String)
                            iparam.MySqlDbType = MySqlDbType.VarChar;
                        if (item.DbType == DbType.AnsiString)
                            iparam.MySqlDbType = MySqlDbType.Text;
                        Cmd.Parameters.Add(iparam);
                    }
                }
                if (Transaction != null)
                    Cmd.Transaction = (MySqlTransaction)Transaction;
                
                effectedRow = Cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            return effectedRow;
        }

        public long GetAutoIncrement(string tablename)
        {
            DataSet ds = new DataSet();
            //ds.Load(connect.ExecuteSelectCommand("select max(ID) from zzzPRDetails"),LoadOption.OverwriteChanges,new string[]{"ID"});
            //string nextIdSql = "select max(ID) form zzzprdetails";
            //ds.Load(ExecuteSelectCommand(nextIdSql), LoadOption.OverwriteChanges, new string[] { "ID" });
            //return ((ds.Tables["ID"].Rows[0]["Auto_increment"].ToString() != "") ? long.Parse(ds.Tables["ID"].Rows[0]["Auto_increment"].ToString()) : 1);
            string nextIdSql = "SELECT LAST_INSERT_ID();";
            ds.Load(ExecuteSelectCommand(nextIdSql), LoadOption.OverwriteChanges, new string[] { tablename });
            return ((ds.Tables[tablename].Rows[0][0].ToString() != "") ? long.Parse(ds.Tables[tablename].Rows[0][0].ToString()) : 1);
        }

        public IDataReader ExecuteSelectCommand(string select_cmd)
        {
            MySqlDataReader resultset = null;
            this.getConnection();

            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                if (Connection.State == ConnectionState.Open)
                {
                    MySqlCommand dbCmd = new MySqlCommand(select_cmd, (MySqlConnection)Connection);
                    if (Transaction != null)
                        dbCmd.Transaction = (MySqlTransaction)Transaction;
                    resultset = dbCmd.ExecuteReader();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return resultset;
        }

        public IDataReader ExecuteSelectCommand(string select_cmd, IParamenter[] param, IParamenter[] paramcondition = null)
        {
            IDataReader effectedRow;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                MySqlCommand Cmd = new MySqlCommand(select_cmd, (MySqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                foreach (IParamenter item in param)
                {
                    MySqlParameter iparam = new MySqlParameter(item.ParameterName, item.Value);
                    if (item.DbType == DbType.String)
                        iparam.MySqlDbType = MySqlDbType.VarChar;
                    if (item.DbType == DbType.AnsiString)
                        iparam.MySqlDbType = MySqlDbType.Text;
                    Cmd.Parameters.Add(iparam);
                }
                if (paramcondition != null)
                {
                    foreach (IParamenter item in paramcondition)
                    {
                        MySqlParameter iparam = new MySqlParameter(item.ParameterName, item.Value);
                        if (item.DbType == DbType.String)
                            iparam.MySqlDbType = MySqlDbType.VarChar;
                        if (item.DbType == DbType.AnsiString)
                            iparam.MySqlDbType = MySqlDbType.Text;
                        Cmd.Parameters.Add(iparam);
                    }
                }
                if (Transaction != null)
                    Cmd.Transaction = (MySqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                throw e;
            }
            return effectedRow;
        }

        public IDataReader ExecuteSelectCommand(string select_cmd, IParamenter[] param)
        {
            IDataReader effectedRow;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                MySqlCommand Cmd = new MySqlCommand(select_cmd, (MySqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                foreach (IParamenter item in param)
                {
                    MySqlParameter iparam = new MySqlParameter(item.ParameterName, item.Value);
                    if (item.DbType == DbType.String)
                        iparam.MySqlDbType = MySqlDbType.Text;
                    if (item.DbType == DbType.AnsiString)
                        iparam.MySqlDbType = MySqlDbType.Text;
                    Cmd.Parameters.Add(iparam);
                }
                if (Transaction != null)
                    Cmd.Transaction = (MySqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                throw e;
            }
            return effectedRow;
        }

        public IDataReader ExecuteSelectCommand(string select_cmd, List<IParamenter> param = null)
        {
            IDataReader effectedRow;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                MySqlCommand Cmd = new MySqlCommand(select_cmd, (MySqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                if (param != null)
                {
                    foreach (IParamenter item in param)
                    {
                        MySqlParameter iparam = new MySqlParameter(item.ParameterName, item.Value);
                        if (item.DbType == DbType.String)
                            iparam.MySqlDbType = MySqlDbType.VarChar;
                        if (item.DbType == DbType.AnsiString)
                            iparam.MySqlDbType = MySqlDbType.Text;
                        Cmd.Parameters.Add(iparam);
                    }
                }
                if (Transaction != null)
                    Cmd.Transaction = (MySqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                throw e;
            }
            return effectedRow;
        }

        public int ExecuteInsertCommand(string insertCmd, IParamenter[] param)
        {
            int effectedRow = 0;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                MySqlCommand Cmd = new MySqlCommand(insertCmd, (MySqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                foreach (IParamenter item in param)
                {
                    MySqlParameter iparam = new MySqlParameter(item.ParameterName, item.Value);
                    if (item.DbType == DbType.String)
                        iparam.MySqlDbType = MySqlDbType.VarChar;
                    if (item.DbType == DbType.AnsiString)
                        iparam.MySqlDbType = MySqlDbType.Text;
                    Cmd.Parameters.Add(iparam);
                }
                if (Transaction != null)
                    Cmd.Transaction = (MySqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            return effectedRow;
        }

        public int ExecuteInsertCommand(string insertCmd, List<IParamenter> param = null)
        {
            int effectedRow = 0;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                MySqlCommand Cmd = new MySqlCommand(insertCmd, (MySqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                if(param != null)
                {
                    foreach (IParamenter item in param)
                    {
                        MySqlParameter iparam = new MySqlParameter(item.ParameterName, item.Value);
                        if (item.DbType == DbType.String)
                            iparam.MySqlDbType = MySqlDbType.VarChar;
                        if (item.DbType == DbType.AnsiString)
                            iparam.MySqlDbType = MySqlDbType.Text;
                        if (item.DbType == DbType.DateTime)
                            iparam.MySqlDbType = MySqlDbType.DateTime;
                        Cmd.Parameters.Add(iparam);
                    }
                }
                if (Transaction != null)
                    Cmd.Transaction = (MySqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            return effectedRow;
        }
       
        public List<IParamenter> GetParameter()
        {
            throw new NotImplementedException();
        }

        public DataSet GetDataSetFormSelectCommand(string select_cmd, IParamenter[] param, IParamenter[] paramcondition = null)
        {
            IDataReader effectedRow;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                MySqlCommand Cmd = new MySqlCommand(select_cmd, (MySqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                foreach (IParamenter item in param)
                {
                    MySqlParameter iparam = new MySqlParameter(item.ParameterName, item.Value);
                    if (item.DbType == DbType.String)
                        iparam.MySqlDbType = MySqlDbType.VarChar;
                    if (item.DbType == DbType.AnsiString)
                        iparam.MySqlDbType = MySqlDbType.Text;
                    Cmd.Parameters.Add(iparam);
                }
                if (paramcondition != null)
                {
                    foreach (IParamenter item in paramcondition)
                    {
                        MySqlParameter iparam = new MySqlParameter(item.ParameterName, item.Value);
                        if (item.DbType == DbType.String)
                            iparam.MySqlDbType = MySqlDbType.VarChar;
                        if (item.DbType == DbType.AnsiString)
                            iparam.MySqlDbType = MySqlDbType.Text;
                        Cmd.Parameters.Add(iparam);
                    }
                }
                if (Transaction != null)
                    Cmd.Transaction = (MySqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteReader();

                if (effectedRow != null)
                {
                    DataSet ds = new DataSet();
                    ds.EnforceConstraints = false;
                    ds.Load(effectedRow, LoadOption.OverwriteChanges, "Table1");
                    effectedRow.Close();
                    return ds;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DataSet GetDataSetFormSelectCommand(string select_cmd, IParamenter[] param)
        {
            IDataReader effectedRow;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                MySqlCommand Cmd = new MySqlCommand(select_cmd, (MySqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                foreach (IParamenter item in param)
                {
                    MySqlParameter iparam = new MySqlParameter(item.ParameterName, item.Value);
                    if (item.DbType == DbType.String)
                        iparam.MySqlDbType = MySqlDbType.Text;
                    if (item.DbType == DbType.AnsiString)
                        iparam.MySqlDbType = MySqlDbType.Text;
                    Cmd.Parameters.Add(iparam);
                }
                if (Transaction != null)
                    Cmd.Transaction = (MySqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteReader();

                if (effectedRow != null)
                {
                    DataSet ds = new DataSet();
                    ds.EnforceConstraints = false;
                    ds.Load(effectedRow, LoadOption.OverwriteChanges, "Table1");
                    effectedRow.Close();
                    return ds;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DataSet GetDataSetFormSelectCommand(string select_cmd, List<IParamenter> param = null)
        {
            IDataReader effectedRow;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                MySqlCommand Cmd = new MySqlCommand(select_cmd, (MySqlConnection)Connection);
                Cmd.CommandType = CommandType.Text;
                if (param != null)
                {
                    foreach (IParamenter item in param)
                    {
                        MySqlParameter iparam = new MySqlParameter(item.ParameterName, item.Value);
                        if (item.DbType == DbType.String)
                            iparam.MySqlDbType = MySqlDbType.VarChar;
                        if (item.DbType == DbType.AnsiString)
                            iparam.MySqlDbType = MySqlDbType.Text;
                        Cmd.Parameters.Add(iparam);
                    }
                }
                if (Transaction != null)
                    Cmd.Transaction = (MySqlTransaction)Transaction;

                effectedRow = Cmd.ExecuteReader();

                if (effectedRow != null)
                {
                    DataSet ds = new DataSet();
                    ds.EnforceConstraints = false;
                    ds.Load(effectedRow, LoadOption.OverwriteChanges, "Table1");
                    effectedRow.Close();
                    return ds;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DataSet GetDataSetFormSelectCommand(string select_cmd)
        {
            MySqlDataReader resultset = null;
            this.getConnection();
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                if (Connection.State == ConnectionState.Open)
                {
                    MySqlCommand dbCmd = new MySqlCommand(select_cmd, (MySqlConnection)Connection);
                    if (Transaction != null)
                        dbCmd.Transaction = (MySqlTransaction)Transaction;
                    resultset = dbCmd.ExecuteReader();

                    if (resultset != null)
                    {
                        DataSet ds = new DataSet();
                        ds.EnforceConstraints = false;
                        ds.Load(resultset, LoadOption.OverwriteChanges, "Table1");
                        resultset.Close();
                        return ds;
                    }
                    else
                    {
                        return null;
                    }
                }
                else
                {
                    return null;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }


        public int ExecuteCreateCommand(string select_cmd)
        {
            this.getConnection();

            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                if (Connection.State == ConnectionState.Open)
                {
                    MySqlCommand dbCmd = new MySqlCommand(select_cmd, (MySqlConnection)Connection);
                    if (Transaction != null)
                        dbCmd.Transaction = (MySqlTransaction)Transaction;
                    return dbCmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return -1;
        }
    }
}